﻿ -- TMovies
DROP TABLE IF EXISTS TMovies;
DROP SEQUENCE IF EXISTS sq; -- The old Movie sequence
DROP SEQUENCE IF EXISTS SQMovies;

 -- TMovies.movie_id sequence
CREATE SEQUENCE SQMovies;
GRANT ALL ON SEQUENCE SQMovies TO "watchMoviesModifiers";

CREATE TABLE TMovies (
movie_id 		INTEGER 		PRIMARY KEY DEFAULT NEXTVAL('SQMovies'),
movie_name		TEXT,
movie_year		SMALLINT,
movie_letter 		"char",
movie_wmaz_code		TEXT,
movie_wmaz_desc		TEXT,
movie_wmaz_parsed 	BOOLEAN 		DEFAULT FALSE,
movie_imdb_code 	TEXT,
movie_when_added 	TIMESTAMP 		DEFAULT CURRENT_TIMESTAMP
);
GRANT ALL ON TABLE TMovies TO "watchMoviesModifiers";

CREATE OR REPLACE FUNCTION F_TMovies_Add_Movie(TEXT,SMALLINT,"char",TEXT) RETURNS VOID AS
	$$ 
		INSERT INTO TMovies (movie_name,movie_year,movie_letter,movie_wmaz_code) SELECT $1,$2,$3,$4 WHERE NOT EXISTS ( SELECT movie_name,movie_year,movie_letter,movie_wmaz_code from TMovies WHERE movie_wmaz_code = $4 ) 
	$$ 
	LANGUAGE SQL;
GRANT ALL ON FUNCTION F_TMovies_Add_Movie(TEXT,SMALLINT,"char",TEXT) TO "watchMoviesModifiers";

CREATE OR REPLACE FUNCTION F_TMovies_Parse_Wmaz(TEXT,TEXT,TEXT) RETURNS VOID AS
	$$ 
		UPDATE TMovies SET movie_wmaz_parsed=TRUE, movie_wmaz_desc=$2, movie_imdb_code=$3 WHERE movie_wmaz_code=$1 
	$$ 
	LANGUAGE SQL;
GRANT ALL ON FUNCTION F_TMovies_Add_Movie(TEXT,SMALLINT,"char",TEXT) TO "watchMoviesModifiers";



